package dao.implementations;

import dao.interfaces.UnitDao;
import engine.Instance;
import engine.Node;
import engine.Unit;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * An MS SQL data access object interface for units.
 * @author Johnny
 */
public class UnitDaoMsSql extends DaoMsSql implements UnitDao {

    private InstanceDaoMsSql instDao;

    public UnitDaoMsSql(InstanceDaoMsSql instDao) {
        this.instDao = instDao;
    }

    public ArrayList<Unit> getUnits() {
        ArrayList<Unit> unitList = new ArrayList<Unit>();
        String query = "SELECT UnitID, Name FROM Units";
        try {
            Statement stmt = getStatement();
            ResultSet rs = stmt.executeQuery(query.toString());
            while (rs.next()) {
                int unitId = rs.getInt(1);
                unitList.add(new Unit(unitId, rs.getString(2),
                        getCourseList(unitId), instDao.getInstances(unitId)));
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(EdgeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }

        return unitList;
    }

    public Unit addUnit(String name) {
        Unit unit = null;
        StringBuffer sb = new StringBuffer();

        sb.append("SET NOCOUNT ON\n");
        sb.append("INSERT INTO Units ( Name ) ");
        sb.append("VALUES ( '");
        sb.append(name);
        sb.append("' )\n");
        sb.append("SELECT SCOPE_IDENTITY()");

        try {
            Statement stmt = getStatement();
            ResultSet rs = stmt.executeQuery(sb.toString());
            rs.next();
            unit = new Unit(rs.getInt(1), name);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }

        return unit;
    }

    public void removeUnit(int unitId) {
        // First we remove all it's instances
        ArrayList<Instance> instList = instDao.getInstances(unitId);
        for (Instance inst : instList) {
            instDao.removeInstance(inst.getId());
        }

        // Next we remove it's course.
        StringBuffer command = new StringBuffer();
        command.append("DELETE FROM Courses WHERE UnitID = ");
        command.append(unitId);
        command.append("\n");
        // Finally we remove the unit itself
        command.append("DELETE FROM Units WHERE UnitID = ");
        command.append(unitId);

        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(command.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateUnit(Unit unit) {
        StringBuffer command = new StringBuffer();
        command.append("UPDATE Units SET Name = '");
        command.append(unit.getName());
        command.append("' WHERE UnitID = ");
        command.append(unit.getId());

        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(command.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void addToCourse(int unitId, int nodeId) {
        // We need to know the number of current courses
        int courseNr = getCourseList(unitId).size();
        // We insert the new node into the courses list
        StringBuffer command = new StringBuffer();
        command.append("INSERT INTO Courses ( UnitID, CourseNodeNr, NodeID ) ");
        command.append("VALUES ( ");
        command.append(unitId);
        command.append(',');
        command.append(courseNr + 1);
        command.append(',');
        command.append(nodeId);
        command.append(")");
        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(command.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
        // Next we extend all schedules.
        extendSchedules(unitId, courseNr + 1);
    }

    public void removeFromCourse(int unitId, int nodeId) {
        // First we ask for the node's position in the middle nodes list.
        int pos = 0;
        ArrayList<Node> course = getCourseList(unitId);
        for (int i = 0; i < course.size(); i++) {
            if (course.get(i).getId() == nodeId) {
                pos = i + 1;
                break;
            }
        }
        // Next we remove it from the course list
        StringBuffer query = new StringBuffer();
        query.append("DELETE FROM Courses WHERE UnitID = ");
        query.append(unitId);
        query.append(" AND CourseNodeNr = ");
        query.append(pos);

        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(query.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
        // Next we have to pull-up the other nodes in the list.
        query = new StringBuffer();
        query.append("UPDATE Courses SET CourseNodeNr = CourseNodeNr - 1 WHERE UnitID = ");
        query.append(unitId);
        query.append(" AND CourseNodeNr > ");
        query.append(pos);
        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(query.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
        // Finally we delete their schedule counterparts and pull-up the rest
        cutSchedules(unitId, pos);
    }

    /**
     * Returns the list of course nodes.
     * @param unitId the id of the unit who's nodes will be returned.
     * @return the list of course nodes.
     */
    private ArrayList<Node> getCourseList(int unitId) {
        ArrayList<Node> courseList = new ArrayList<Node>();
        StringBuffer query = new StringBuffer();
        query.append("SELECT N.NodeID, X, Y, Name FROM Courses AS C, Nodes AS N ");
        query.append("WHERE C.NodeID = N.NodeID AND UnitID = ");
        query.append(unitId);
        query.append(" ORDER BY CourseNodeNr");

        try {
            Statement stmt = getStatement();
            ResultSet rs = stmt.executeQuery(query.toString());
            while (rs.next()) {
                courseList.add(new Node(rs.getInt(1), rs.getFloat(2),
                        rs.getFloat(3), rs.getString(4)));
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(EdgeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }

        return courseList;
    }

    /**
     * Extends all schedules of a unit to the new size.
     * @param unitId the id of the unit who's schedules will be extended.
     * @param newNr the new size.
     */
    private void extendSchedules(int unitId, int newNr) {
        StringBuffer command = new StringBuffer();
        ArrayList<Instance> instList = instDao.getInstances(unitId);
        for (Instance inst : instList) {
            command.append("INSERT INTO Schedules ( InstanceID, TimeNr, Time ) ");
            command.append("VALUES ( ");
            command.append(inst.getId());
            command.append(',');
            command.append(newNr);
            command.append(',');
            command.append(inst.getSchedules().get(newNr - 2).getTotalSeconds() + 1);
            command.append(")\n");
        }
        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(command.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void cutSchedules(int unitId, int pos) {
        StringBuffer command = new StringBuffer();
        ArrayList<Instance> instList = instDao.getInstances(unitId);
        for (Instance inst : instList) {
            command.append("DELETE FROM Schedules WHERE InstanceID = ");
            command.append(inst.getId());
            command.append(" AND TimeNr = ");
            command.append(pos);
            command.append("\n");
            // We pull up the rest
            command.append("UPDATE Schedules SET TimeNr = TimeNr - 1 WHERE InstanceID = ");
            command.append(inst.getId());
            command.append(" AND TimeNr > ");
            command.append(pos);
            command.append("\n");
        }
        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(command.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
